{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2 as pg\n",
    "import random"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 获取DB连接对象\n",
    "4个用户 \\\n",
    "    pm:      create 用户 数据库 \\\n",
    "    t_coder: 老师端, select 教师表, insert\\update 成绩表 \\\n",
    "    s_coder: 学生端, select 学生表 课程表 成绩表 \\ \n",
    "    affair:  教务处, insert 教师表 学生表 课程表 \\"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_conn(user='pm'):\n",
    "    conn = pg.connect(database=\"db_school\", \n",
    "                  user=user, \n",
    "                  password=\"123456twG\", \n",
    "                  host=\"127.0.0.1\", port=\"7654\")\n",
    "    return conn"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 从CSV文件导入\n",
    "Day1, affair用户, 将student.tsv, teacher.tsv, course.tsv导入到数据库中"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def insert_tsv(conn, table_name, tsv_path, sep='\\t'):\n",
    "    print(\"Opened database successfully\")\n",
    "    cur = conn.cursor()\n",
    "\n",
    "    copy_query = f\"COPY public.{table_name} FROM STDIN WITH (FORMAT CSV, HEADER TRUE, DELIMITER E'\\\\t', ENCODING 'UTF-8')\"\n",
    "    with open(tsv_path, 'r', encoding='utf-8') as file:\n",
    "        cur.copy_expert(copy_query, file)\n",
    "        print(f\"Copy {tsv_path} ok\")\n",
    "    # 提交事务\n",
    "    conn.commit()\n",
    "    # 关闭数据库连接\n",
    "    cur.close()\n",
    "    conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = get_conn(user='affair')\n",
    "insert_tsv(conn, \"teacher\", '../data/teacher.tsv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Opened database successfully\n",
      "Copy ../data/student.tsv ok\n",
      "Opened database successfully\n",
      "Copy ../data/course.tsv ok\n"
     ]
    }
   ],
   "source": [
    "conn2 = get_conn(user='affair')\n",
    "conn3 = get_conn(user='affair')\n",
    "insert_tsv(conn2, \"student\", '../data/student.tsv')\n",
    "insert_tsv(conn3, \"course\", '../data/course.tsv')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 向score添加数据\n",
    "Day2, t_coder用户，向score表插入数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def insert_score(conn):\n",
    "    student_id = range(20230001, 20230041)\n",
    "    course_id = range(10, 15)\n",
    "    cur = conn.cursor()\n",
    "    \n",
    "    insert_value = []\n",
    "    for s_id in student_id:\n",
    "        for c_id in course_id:\n",
    "            random_score = random.randint(75, 95)\n",
    "            insert_value.append((s_id, c_id, random_score))\n",
    "\n",
    "    insert_sql = \"INSERT INTO public.score (student_id, course_id, score) VALUES (%s, %s, %s)\"\n",
    "    cur.executemany(insert_sql, insert_value)\n",
    "    conn.commit()\n",
    "    cur.close()\n",
    "    conn.close()\n",
    "    \n",
    "\n",
    "    \n",
    "# 修改成绩\n",
    "def update_score(conn, repete_times=10):\n",
    "    cur = conn.cursor()    \n",
    "        \n",
    "    update_value = []\n",
    "    for i in range(repete_times):\n",
    "        s_id = random.randint(20230001, 20230040)\n",
    "        c_id = random.randint(10, 14)\n",
    "        det_score = random.randint(-5, 0)\n",
    "        update_value.append((det_score, s_id, c_id))\n",
    "        \n",
    "    update_sql = \"UPDATE public.score SET score = score+(%s) WHERE student_id=(%s) AND course_id=(%s) \"\n",
    "    cur.executemany(update_sql, update_value)\n",
    "    conn.commit()\n",
    "    cur.close()\n",
    "    conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = get_conn(user='t_coder')\n",
    "insert_score(conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn_update_score = get_conn(user='t_coder')\n",
    "update_score(conn_update_score)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 查询teacher,student,score\n",
    "Day1-5: t_coder查询teacher表，s_coder查询student表、course表 \\\n",
    "Day2: teacher查询score, 学生查询score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 对表{table_name}指定{query_id}查询{repete_times}次\n",
    "# 查询student teacher course都是调用这个函数\n",
    "def select(conn, table_name, repete_times, query_id, id_range):\n",
    "    cur = conn.cursor()    \n",
    "    query_value = []    \n",
    "    for t in range(repete_times):\n",
    "        query_value.append((random.randint(id_range[0], id_range[1]),))\n",
    "    query_sql = f\"SELECT * FROM public.{table_name} WHERE {query_id}=(%s)\"\n",
    "    \n",
    "    cur.executemany(query_sql, query_value)\n",
    "    conn.commit()\n",
    "    cur.close()\n",
    "    conn.close()\n",
    "    \n",
    "\n",
    "# 学生查询课表\n",
    "def select_course(conn, repete_times):\n",
    "    cur = conn.cursor()        \n",
    "    query_sql = \"SELECT course_id, cname, name, point FROM public.course c, public.teacher t where c.teacher_id = t.teacher_id\"    \n",
    "    \n",
    "    for i in range(repete_times):\n",
    "        cur.execute(query_sql)\n",
    "        result = cur.fetchall()\n",
    "    conn.commit()\n",
    "    cur.close()\n",
    "    conn.close()\n",
    "\n",
    "\n",
    "# 学生查询成绩\n",
    "def select_score(conn):\n",
    "    cur = conn.cursor()        \n",
    "    query_sql = \"\"\"SELECT s.student_id, s.\"name\", c.cname, s2.score \n",
    "                    FROM public.score s2, public.course c, public.student s \n",
    "                    WHERE s2.course_id = c.course_id AND s2.student_id = s.student_id AND s.student_id=(%s)\"\"\"    \n",
    "    query_value = [(i,) for i in range(20230001, 20230041)]\n",
    "    \n",
    "    cur.executemany(query_sql, query_value)    \n",
    "    conn.commit()\n",
    "    cur.close()\n",
    "    conn.close()    \n",
    "\n",
    "\n",
    "# 老师查询指定课程的所有成绩\n",
    "def select_score_teacher(conn):\n",
    "    cur = conn.cursor()    \n",
    "    query_value = [(i,) for i in range(10, 15)]\n",
    "    query_sql = \"SELECT * FROM public.score WHERE course_id=(%s) ORDER BY score desc;\"\n",
    "    \n",
    "    cur.executemany(query_sql, query_value)\n",
    "    conn.commit()\n",
    "    cur.close()\n",
    "    conn.close()\n",
    "    "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "执行查询学生信息、老师信息、课程信息脚本"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn_s = get_conn(user='s_coder')\n",
    "conn_s2 = get_conn(user='s_coder')\n",
    "conn_t = get_conn(user='t_coder')\n",
    "\n",
    "\n",
    "s_repete = random.randint(70,100)\n",
    "s2_repete = s_repete + random.randint(5,10)\n",
    "t_repete = random.randint(10,30)\n",
    "\n",
    "# s_coder查询学生信息\n",
    "select(conn_s, \"student\", s_repete, \"student_id\", (20230001, 20230040))\n",
    "# s_coder查询课程信息\n",
    "select_course(conn_s2, s2_repete)\n",
    "# t_coder查询教师信息\n",
    "select(conn_t, \"teacher\", t_repete, \"teacher_id\", (101, 105))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "查询成绩"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn_score = get_conn(user='s_coder')\n",
    "conn_score_t = get_conn(user='t_coder')\n",
    "\n",
    "# s_coder查询成绩\n",
    "select_score(conn_score)\n",
    "# t_coder按照课程号查询成绩\n",
    "select_score_teacher(conn_score_t)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "opengauss",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.16"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
